﻿
CREATE OR REPLACE FUNCTION public.alertsadd(
    p_bill varchar,---表名
    p_billid integer,----表ID
    p_status integer,---审核后状态 0反审核,1--提交审核,2审核
    p_workflow_transitionid integer,--流程动作ID
    p_tijiao integer,----1合同提交时是否提醒
    p_userid integer,---用户ID
    p_message varchar,
    p_org_id integer
    )
  RETURNS boolean  AS
$BODY$
declare  
    v_alertsid  integer;
    v_query_id  integer;
    v_billname character varying(255);
    v_billcode character varying(255);
    v_timelimit integer;
    v_timemsg character varying(255);
    v_userid  integer;
begin
	select nextval('public.alerts_id_seq') into v_alertsid;
	select COALESCE(min(id),0) into v_query_id From public.queries;
	select code,name,userid
	into  v_billcode,v_billname,v_userid
	From 
	(select 'contractapply' as tablename,id,code,'合同申请单' as name,userid from  public.contractapply union all
	 select 'contracts' as tablename,id,code,'合同' as name,userid from  public.contracts union all
	 select 'contractseal' as tablename,id,code,'合同用印申请单' as name,userid from  public.contractseal union all
	 select 'contractrecord' as tablename,id,code,'合同备案申请单' as name,userid from  public.contractrecord  union all
	 select 'contractchange' as tablename,id,code,'合同变更申请单' as name,userid from  public.contractchange union all
	 select 'contractprogress' as tablename,id,code,'合同违约登记单' as name,userid from  public.contractprogress  union all
	 select 'contractsettle' as tablename,id,code,'合同付款单' as name,userid from  public.contractsettle union all
	select 'contractstop' as tablename,id,code,'合同终止申请单' as name,userid from  public.contractstop union all
	select 'contractfinish' as tablename,id,code,'合同验收申请单' as name,userid from  public.contractfinish) AB
	where tablename=p_bill and id=p_billid;
  if v_query_id=0 then
	return false;
  end if;
  delete From alert_subscriptions where destination_id in(select t1.id From notification_destinations t1,users t2  where t1.user_id =t2.id and t1.name <> t2.email) ;
  delete from  notification_destinations where id in (select t1.id From notification_destinations t1,users t2  where t1.user_id =t2.id and t1.name <> t2.email) ;
  
  if p_tijiao=1 then
      select COALESCE(t2.timelimit,0)
      into v_timelimit
      From public.workflow_transition t1 inner join public.workflow_bill_state t2 on t1.workflowid=t2.workflowid and t1.workflow_stateid=t2.workflow_stateid
      where t1.id=p_workflow_transitionid;
  else
      select COALESCE(t2.timelimit,0)
      into v_timelimit
      From public.workflow_transition t1 inner join public.workflow_bill_state t2 on t1.workflowid=t2.workflowid and t1.next_workflow_stateid=t2.workflow_stateid
      where t1.id=p_workflow_transitionid;
  end if;
  v_timemsg :='';
  if v_timelimit>0 then
       v_timemsg :='限在' || cast(v_timelimit as VARCHAR) || '天内审核';
  end if;
  if p_status =1 then
	  insert into public.notification_destinations(
	  id,org_id,user_id,name,type,options,
	  created_at)
	  select  nextval('public.notification_destinations_id_seq'),p_org_id,id,email,'email','{"addresses": "' || name || '"}',
	  now() from public.users where id in(select t2.id From public.workflow_transition t1 , public.users t2 
	   where ((t2.id = any(t1.allow_user)) or (t1.allow_group && t2.groups)) and t1.id=p_workflow_transitionid)
	  and id not in(select user_id from public.notification_destinations)
	  and email not in(select name from public.notification_destinations);
	--raise notice 'notification_destinations: %,%',v_orgid,p_workflow_transitionid;
	  
	 insert into public.alerts(
	  updated_at,created_at,id,name,
	  contract_id,user_id,options,
	  state,query_id)
	  select now(),now(),v_alertsid,v_billname || ':' || v_billcode || (case when p_workflow_transitionid=0 then '已提交' else '待审核' end) || v_timemsg,
	  p_billid,p_userid,'{"op":">","value":1,"muted":false,"column":"'|| p_billid || '","custom_subject":"' || v_billname || '","custom_body":"' || p_message || v_billname || ':' || v_billcode  || (case when p_workflow_transitionid=0 then '已提交' else '待审核' end) || '"}',
	  'unknown',v_query_id;
	---raise notice 'alerts: %,%,%,%,%,%,%,%',v_alertsid,v_billname,v_billcode,p_workflow_transitionid,p_billid,p_userid,p_message,v_query_id;

	---raise notice 'Parameter is: %,%',v_alertsid,p_workflow_transitionid;
  	insert into public.alert_subscriptions(
	  updated_at,created_at,user_id,destination_id,
	  alert_id)
	select now(),now(),t2.id,t1.id,v_alertsid 
        from public.notification_destinations t1 inner join public.users t2 on t1.user_id=t2.id 
	  where  t2.id in (select t2.id From public.workflow_transition t1 , public.users t2 
	   where ((t2.id = any(t1.allow_user)) or (t1.allow_group && t2.groups)) and t1.id=p_workflow_transitionid) and t2.id <>p_userid;
	   
	--update public.notification_destinations set name=t1.email
	--from public.users t1 where t1.id= notification_destinations.user_id
	--and notification_destinations.id in(select destination_id from public.alert_subscriptions where alert_id=v_alertsid);
  elsif p_status =2 then
	  insert into public.notification_destinations(
	  id,org_id,user_id,name,type,options,
	  created_at)
	  select  nextval('public.notification_destinations_id_seq'),p_org_id,id,email,'email','{"addresses": "' || name || '"}',
	  now() from public.users where id in(select distinct userid From public.workflow_action where bill_enum=p_bill and billid=p_billid)
	  and id not in(select user_id from public.notification_destinations);
	  
	 insert into public.alerts(
	  updated_at,created_at,id,name,
	  contract_id,user_id,options,
	  state,query_id)
	  select now(),now(),v_alertsid,v_billname || ':' || v_billcode || (case when p_status=2 then '已审核' else '已驳回' end),
	  p_billid,p_userid,'{"op":">","value":1,"muted":false,"column":"'|| p_billid || '","custom_subject":"' || v_billname || '","custom_body":"' || p_message || v_billname || ':' || v_billcode  || (case when p_status=2 then '已审核' else '已驳回' end)|| '"}',
	  'unknown',v_query_id;

  	insert into public.alert_subscriptions(
	  updated_at,created_at,user_id,destination_id,
	  alert_id)
	select now(),now(),t2.id,t1.id,v_alertsid 
        from 
	public.notification_destinations t1 inner join public.users t2 on t1.user_id=t2.id 
	where  t2.id =v_userid  and t2.id <>p_userid;

	--update public.notification_destinations set name=t1.email
	--from public.users t1 where t1.id= notification_destinations.user_id
	--and notification_destinations.id in(select destination_id from public.alert_subscriptions where alert_id=v_alertsid);
  end if;
   return true; 
  EXCEPTION
    WHEN others THEN    
    return false; 
END;  
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
